package com.lxf.dao.Impl;

import com.lxf.bean.Student;
import com.lxf.dao.DBUtils;
import com.lxf.dao.StudentDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {

    @Override
    public List<Student> getStudents(String username, String stuno, int sex, int pageIndex, int pageSize) {
        ArrayList<Student> list = null;
        try {
            ArrayList param = new ArrayList();
            StringBuffer sql = new StringBuffer(" select * from student where 1=1 ");

            if (username != null && username.length() > 0) {
                sql.append(" and stuname = like ? ");
                param.add("%"+username+"%");
            }
            if (stuno != null && stuno.length() > 0) {
                sql.append(" and stuno=? ");
                param.add(stuno);
            }
            if (sex != -1) {
                sql.append(" and sex =? ");
                param.add(sex);
            }
                sql.append(" limit ?,? ");  //limit 0 ,5  limit (pageIndex-1)*pageSize,pageSize
                param.add((pageIndex-1)*pageSize);
                param.add(pageSize);
            for (int i=0;i<param.size();i++){
                System.out.println("param的"+i+"值："+param.get(i));
            }
            System.out.println("sql语句::"+sql.toString());
            resultSet = query(sql.toString(), param);
            list = new ArrayList<>();
            Student student = null;
            if (resultSet == null) {
                return null;
            }
            while (resultSet.next()) {
                student = new Student();
                student.setAddress(resultSet.getString("address"));
                student.setEmail(resultSet.getString("email"));
                student.setGid(Integer.parseInt(resultSet.getString("gid")));
                student.setPhone(resultSet.getString("phone"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setPolitics(resultSet.getString("politics"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setEmail(resultSet.getString("email"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setRegistered(resultSet.getString("registered"));
                student.setSex(Integer.parseInt(resultSet.getString("sex")));
                student.setStuNo(resultSet.getString("stuno"));//Stuno
                student.setStuName(resultSet.getString("stuname"));//StuName
                student.setState(Integer.parseInt(resultSet.getString("state")));
                student.setStuId(Integer.parseInt(resultSet.getString("stuId")));
                System.out.println("数据库："+student.toString());
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return list;
    }

    @Override
    public int total(String username, String stuno, int sex) {
        int total = 0;
        try {
            ArrayList param = new ArrayList();
            StringBuffer sql = new StringBuffer(" select count(*) from student where 1=1 ");

            if (username != null && username.length() > 0) {
                sql.append(" and stuname = like ? ");
                param.add("%" + username + "%");
            }
            if (stuno != null && stuno.length() > 0) {
                sql.append(" and stuno = ? ");
                param.add(stuno);
            }
            if (sex != -1) {
                sql.append(" and sex = ? ");
                param.add(sex);
            }
            resultSet = query(sql.toString(), param);
            if (resultSet == null) {
                return 0;
            }
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }
}
